package com.demo.controller;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.demo.vo.StoreGoodsImportDetailHeadVO;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;


/**
 *  参考：https://blog.csdn.net/Mrs_chens/article/details/90694987
 */
@RestController
public class ExcelController {

    public static void main(String[] args) {
        //创建工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        //创建一个sheet
        XSSFSheet sheet = wb.createSheet();

        // 创建单元格样式
        XSSFCellStyle style =  wb.createCellStyle();
        style.setFillForegroundColor((short)4); //设置要添加表格北京颜色
        style.setFillPattern(FillPatternType.NO_FILL); //solid 填充
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); //文字水平居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//文字垂直居中
        style.setBorderBottom(BorderStyle.THIN); //底边框加黑
        style.setBorderLeft(BorderStyle.THIN);  //左边框加黑
        style.setBorderRight(BorderStyle.THIN); // 有边框加黑
        style.setBorderTop(BorderStyle.THIN); //上边框加黑
        //为单元格添加背景样式
        for (int i = 0; i < 6; i++) { //需要6行表格
            Row row =	sheet.createRow(i); //创建行
            for (int j = 0; j < 6; j++) {//需要6列
                row.createCell(j).setCellStyle(style);
            }
        }


        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//合并单元格，cellRangAddress四个参数，第一个起始行，第二终止行，第三个起始列，第四个终止列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 5));

        //tian入数据
        XSSFRow row = sheet.getRow(0); //获取第一行
        row.getCell(1).setCellValue("2018期末考试"); //在第一行中创建一个单元格并赋值
        XSSFRow row1 = sheet.getRow(1); //获取第二行，为每一列添加字段
        row1.getCell(1).setCellValue("语文");
        row1.getCell(2).setCellValue("数学");
        row1.getCell(3).setCellValue("英语");
        row1.getCell(4).setCellValue("物理");
        row1.getCell(5).setCellValue("化学");
        XSSFRow row2 = sheet.getRow(2); //获取第三行
        row2.getCell(0).setCellValue("张三");
        XSSFRow row3 = sheet.getRow(3); //获取第四行
        row3.getCell(0).setCellValue("张三");
        XSSFRow row4 = sheet.getRow(4); //获取第五行
        row4.getCell(0).setCellValue("张三");
        XSSFRow row5 = sheet.getRow(5); //获取第五行
        row5.getCell(0).setCellValue("张三");
        //将数据写入文件
        try {
            FileOutputStream out = new FileOutputStream("D:\\测试.xlsx");
            wb.write(out);
        }catch (Exception e){
            e.printStackTrace();
        }
    }






    /**

     * 导出excel

     */

    @RequestMapping("/export")
    public void expr(HttpServletRequest request, HttpServletResponse response)throws Exception{

        SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
        String fileName = sdf.format(new Date()) +".xlsx";
        //声明一个工作簿
        XSSFWorkbook workbook=new XSSFWorkbook();
        //自动换行
        XSSFCellStyle setBorder=workbook.createCellStyle();
        //生成一表格
        XSSFSheet sheet=workbook.createSheet("频道列表");
        XSSFRow row =null;
        String[] ordered = {"貂蝉","诸葛亮","吕布","貂蝉","鲁班","诸葛亮","貂蝉","鲁班","诸葛亮"};
        List<String> list = Arrays.asList(ordered);
        row=sheet.createRow(0);
        //行高
        row.setHeight((short) (26.25*20));
        row.createCell(0).setCellValue("ID");
        row.createCell(1).setCellValue("频道名称");
        row.createCell(2).setCellValue("排序");
        for (int i=0;i<list.size();i++){
        row=sheet.createRow(i+1);
        row.createCell(0).setCellValue(list.get(i));
        row.createCell(1).setCellValue(list.get(i));
        row.createCell(2).setCellValue(list.get(i));
    }
        sheet.setDefaultRowHeight((short) (16.5 *20));
        //列宽自适应
        for (int i =0; i <=list.size() ; i++) {
            sheet.autoSizeColumn(i);
        }
    try {
    // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type","application/vnd.ms-excel");
    // 下载文件的默认名称
            response.setHeader("Content-disposition","attachment;filename="+ URLEncoder.encode(fileName,"utf-8"));
        }catch (Exception e) {
            e.printStackTrace();
        }
    try{
            workbook.write(response.getOutputStream());
        }catch(Exception e){
            workbook.close();
        }
    }




    /**
     * 数据量少的(20W以内吧)：一个SHEET一次查询导出
     * @param response
     * @throws Exception
     */
    @RequestMapping("/export1")
    public void exportSysSystemExcel(HttpServletResponse response) throws Exception {

        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            // 设置EXCEL名称
            String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
            // 设置SHEET名称
            Sheet sheet = new Sheet(1, 0);
            sheet.setSheetName("系统列表sheet1");
            // 设置标题
            Table table = new Table(1);
            List<List<String>> titles = new ArrayList<List<String>>();
            titles.add(Arrays.asList("用户ID"));
            titles.add(Arrays.asList("名称"));
            titles.add(Arrays.asList("年龄"));
            titles.add(Arrays.asList("生日"));
            table.setHead(titles);
            // 查询数据导出即可 比如说一次性总共查询出100条数据
            List<List<String>> userList = new ArrayList<>();
            for (int i = 0; i < 20; i++) {
                userList.add(Arrays.asList("ID_" + i, "小明" + i, String.valueOf(i), new Date().toString()));
            }
            writer.write0(userList, sheet, table);

            Table table2 = new Table(2);
            List<List<String>> titles2 = new ArrayList<List<String>>();
            titles2.add(Arrays.asList("1列","2列","3列","4列","5列","6列","7列","8列"));
            titles2.add(Arrays.asList("1","2","3","4","5","6","7","8"));
            table2.setHead(titles2);
            List<List<String>> userList2 = new ArrayList<>();
            userList2.add(Arrays.asList("1", "王", "王", "王", "王", "王", "王", "王"));
            writer.write0(userList2, sheet,table2);
            // 下载EXCEL
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            writer.finish();
            out.flush();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

    }

    /**
     * 自定义Excel
     * @param response
     */
    @RequestMapping("/orderExport")
    public void orderExport(HttpServletResponse response){
        //进行查询并导出
            //导出文件的标题
            String title = "商城订单"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls";
            //设置表格标题行
            String[] headers = new String[] {"序号","订单号", "花匠名称","订单时间","状态", "消费总额", "退款金额"};
            List<Object[]> dataList = new ArrayList<Object[]>();
            Object[] objs = null;
                objs = new Object[headers.length];
                objs[0] = "1001";//序号
                objs[1] = "2000000000000000002";//订单号
                objs[2] = "wang";//花匠信息
                objs[3] = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
                objs[4] = "未发货，申请退款";//状态
                objs[5]="100.0";//消费金额
                objs[6] = "0.0";
                //数据添加到excel表格
                dataList.add(objs);
        StoreGoodsImportDetailHeadVO storeGoodsImportDetailHeadVO = new StoreGoodsImportDetailHeadVO();
        storeGoodsImportDetailHeadVO.setStoreGoodsImportId(Long.parseLong("1000000000000000001"));
        storeGoodsImportDetailHeadVO.setTotalAmount(100.00);
        storeGoodsImportDetailHeadVO.setTotalNum(100);
        storeGoodsImportDetailHeadVO.setOrderState("待发货");
        storeGoodsImportDetailHeadVO.setPayTime(new Date());
        storeGoodsImportDetailHeadVO.setStoreAddress("北京市|北京城区|昌平区|白庙村1号");
        storeGoodsImportDetailHeadVO.setUsername("王");
        storeGoodsImportDetailHeadVO.setPhone("17701000001");
        storeGoodsImportDetailHeadVO.setLogistics("中通物流");
        storeGoodsImportDetailHeadVO.setLogisticsCode("8112019061400000");
            //使用流将数据导出
            OutputStream out = null;
            try {
                //防止中文乱码
                response.setHeader("content-Type","application/octet-stream");
                // 用replace("+","%20")是因为encode会把空格编码成+，所以要替换回空格
                response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(title, "UTF-8").replace("+","%20"));
                response.setCharacterEncoding("UTF-8");
                out = response.getOutputStream();
                //ExportExcel ex = new ExportExcel(title, headers, dataList);//有标题
                ExportExcelSeedBack ex = new ExportExcelSeedBack(title, headers, dataList,storeGoodsImportDetailHeadVO);//没有标题
                ex.export1(out);
            } catch (Exception e) {
                e.printStackTrace();
            }
    }


}
